package io.jpress.addon.oracle;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbPro;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;

import io.jboot.utils.StrUtil;

public class OracleTableUtil {
	private DbPro db = null;
	private String tabname;
	private String pk;

	public OracleTableUtil(String dbsource, String tabname, String pk) {
		this.tabname = tabname;
		this.pk = pk;
		if (!StrUtil.isBlank(dbsource))
			this.db = Db.use(dbsource);
		else
			this.db = Db.use();
	}

	public boolean insert(Record record) {
		return db.save(this.tabname, pk, record);
	}

	public boolean delete(Record record) {
		return db.delete(this.tabname, pk, record);
	}

	public boolean update(Record record) {
		return db.update(this.tabname, pk, record);
	}

	public Page<Record> page(SqlPara sqlPara, Integer pageNumber, Integer pageSize) {
		if (StrUtil.isBlank(sqlPara.getSql())) {
			sqlPara.setSql("select * from " + this.tabname);
		}
		return db.paginate(pageNumber, pageSize, sqlPara);
	}

	public List<Record> getAll() {
		return db.find("select * from " + this.tabname);
	}
	
	public boolean merge(Record r,String key) {
		Map<String,Object> datas=r.getColumns();
		List<Object> args= new ArrayList<Object>();
		if(datas.size()<2)
			return false;
	
		String sql="merge into "+ this.tabname+" a ";
		String selSql="using (select ";
		String updSql="when matched then update set ";
		String insSql="when not matched then insert (";
		String insSqlVal="values(";
		
		boolean first1=true;
		boolean first2=true;
		for (Entry<String,Object> entry:datas.entrySet()) {
			if(first1) {
				selSql = selSql + "? "+entry.getKey();
				insSql = insSql + ""+entry.getKey();
				insSqlVal =insSqlVal + "b."+entry.getKey();
				first1=false;
			}
			else {
				selSql = selSql + ",? "+entry.getKey();
				insSql = insSql + ","+entry.getKey();
				insSqlVal =insSqlVal + ",b."+entry.getKey();
			}
				
			args.add(entry.getValue());
			if(!entry.getKey().equalsIgnoreCase(key)) {
				if(first2) {
					updSql = updSql+ "a."+entry.getKey()+"="+"b."+entry.getKey();
					first2=false;
				}
				else
					updSql = updSql+ ",a."+entry.getKey()+"="+"b."+entry.getKey();
			}
		}
		selSql = selSql + " from dual ) b\n";
		selSql = selSql + "on ( a."+key+" = b."+key+" ) ";
		insSql = insSql +")";
		insSqlVal = insSqlVal +")";
		sql=sql+"\n"+selSql+"\n"+updSql+"\n"+insSql +" "+ insSqlVal;
		this.db.update(sql,args.toArray());
		return true;
	}
	
}
